﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.Data;
using Entity;

namespace DAO
{
    public class XuatHangDAO
    {
        public static DataTable LayDanhSachTenSP()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                String cmdText = "SELECT TenSP FROM dbo.fn_LayTenSP()";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs;
        }

        public static DataTable LayThongTinKhachHang(String makh)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@Makh", makh);

                String cmdText = "SELECT TenKH, DiaChi, SoDienThoai, GiamGia " +
                    "FROM KHACH_HANG_THAN_THIET a, LOAI_KHACH_HANG b " +
                    "WHERE a.MaLoaiKhachHang = b.MaLoaiKhachHang and CMND = @Makh";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText, par).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return rs;
        }

        public static String LayMaPhieuXuat()
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                String cmdText = "Select MaPhieu from PHIEU_XUAT_HANG " +
                                "where stuff(MaPhieu,1,6,'') = (Select Max(stuff(MaPhieu,1,6,'')) from PHIEU_XUAT_HANG)";
                rs = SqlHelper.ExecuteDataset(connect, CommandType.Text, cmdText).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }

            return rs.Rows[0][0].ToString();
        }

        public static void XuatHang(PhieuXuatHang ph, ListSP_Xuat list)
        {
            SqlConnection connect = null;
            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }
                SqlParameter[] param = new SqlParameter[7];
                param[0] = new SqlParameter("@tennv", ph.tennv);
                param[1] = new SqlParameter("@tongtien", ph.tongtien);
                param[2] = new SqlParameter("@ngayxuat", ph.ngayxuat);
                param[3] = new SqlParameter("@giamgia", ph.giamgia);
                param[4] = new SqlParameter("@tenkh", ph.tenkh);
                param[5] = new SqlParameter("@diachi", ph.diachi);
                param[6] = new SqlParameter("@sodienthoai", ph.sodienthoai);
                SqlHelper.ExecuteNonQuery(connect, CommandType.StoredProcedure, "usp_PhieuXuatHang", param);

                SanPham_Xuat spxuat = new SanPham_Xuat();
                for (int i = 0; i < list.sp.Count; i++)
                {
                    spxuat = list.sp[i];
                    SqlParameter[] param2 = new SqlParameter[4];
                    param2[0] = new SqlParameter("@maphieuxuat", LayMaPhieuXuat());
                    param2[1] = new SqlParameter("@masp", spxuat.masp);
                    param2[2] = new SqlParameter("@soluong", spxuat.soluong);
                    param2[3] = new SqlParameter("@giaban", spxuat.dongia);
                    String cmdText2 = "INSERT INTO CHI_TIET_XUAT_HANG(MaPhieuXuat, MaSP, GiaBan, SoLuong) values " +
                        "(@maphieuxuat, @masp, @giaban, @soluong)";
                    SqlHelper.ExecuteNonQuery(connect, CommandType.Text, cmdText2, param2);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

            finally
            {
                if (connect != null)
                    connect.Dispose();
            }
        }

        public static float SoLuongSPCon(String masp)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[1];
                par[0] = new SqlParameter("@masp", masp);
                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_SoLuongSPCon", par).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return float.Parse(rs.Rows[0][0].ToString());
        }

        public static int LayGiamGia(String tenkh, String diachi)
        {
            SqlConnection connect = null;
            DataTable rs = null;

            try
            {
                try
                {
                    connect = Connection.GetConnection();
                }
                catch
                {
                    throw new Exception("The connection with the database can´t be established");
                }

                SqlParameter[] par = new SqlParameter[2];
                par[0] = new SqlParameter("@tenkh", tenkh);
                par[1] = new SqlParameter("@diachi", diachi);
                rs = SqlHelper.ExecuteDataset(connect, CommandType.StoredProcedure, "usp_LayGiamGia", par).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                if (rs != null)
                    ((IDisposable)rs).Dispose();

                if (connect != null)
                    connect.Dispose();
            }
            return int.Parse(rs.Rows[0][0].ToString());
        }
    }
}
